﻿using SuporeSliplampApp.Base;
using SuporeSliplampApp.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Controls;
using System.Windows.Media.Imaging;
//using System.Data.Sql;
//using System.Data.SqlClient;
using System.Data.SQLite;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.StartPanel;
using System.Windows;
using System.ServiceModel.Configuration;
using static SuporeSliplampApp.Util.uvcham;
using System.Windows.Media.Animation;
using ControlzEx.Standard;
using System.Data.SqlClient;
//using System.Data.Sql;

namespace SuporeSliplampApp.DataAccess
{
    public class SqlHelper
    {
        public SQLiteConnection Conn { get; set; }

        public SQLiteCommand Comm { get; set; }
        public SQLiteDataAdapter adapter { get; set; }

        private void Dispose()
        {
            adapter?.Dispose();
            Comm?.Dispose();
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
                Conn = null;
            }
        }

        private bool DBConnection()
        {
            string connStr = ConfigurationManager.ConnectionStrings["slip"].ConnectionString;
            if (Conn == null) Conn = new SQLiteConnection(connStr);

            try
            {
                Conn.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool CheckUserInfo(string userID, string pwd)
        {
            try
            {
                if (DBConnection())
                {
                    string userSql = "SELECT * FROM user_tb WHERE user_ID = @user_ID and pwd =@pwd";
                    adapter = new SQLiteDataAdapter(userSql, Conn);
                    adapter.SelectCommand.Parameters.AddWithValue("@user_ID", userID);
                    adapter.SelectCommand.Parameters.AddWithValue("@pwd", Md5Provider.GetMD5String(pwd + "@" + userID.ToLower()));

                    DataTable dt = new DataTable();
                    int count = adapter.Fill(dt);

                    return count > 0;
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }


        public bool SavePatient(PatientModel patient)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = "INSERT INTO patient_tb(p_ID,p_name,sex,birth,adress,telphone,email,creator,insertDate,updateDate) VALUES (@p_ID,@p_name,@sex,@birth,@adress,@telphone,@email,@creator,@insertDate,@updateDate)";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    {
                        cmd.Parameters.AddWithValue("@p_ID", patient.P_ID);
                        cmd.Parameters.AddWithValue("@p_name", patient.Name);
                        cmd.Parameters.AddWithValue("@sex", patient.SEX);
                        cmd.Parameters.AddWithValue("@birth", patient.Birth);
                        cmd.Parameters.AddWithValue("@adress", patient.Adreess);
                        cmd.Parameters.AddWithValue("@telphone", patient.PhoneNum);
                        cmd.Parameters.AddWithValue("@email", patient.Email);
                        cmd.Parameters.AddWithValue("@creator", patient.Creator);
                        cmd.Parameters.AddWithValue("@insertDate", DateTime.Now);
                        cmd.Parameters.AddWithValue("@updateDate", DateTime.Now);
                        return cmd.ExecuteNonQuery() > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public bool deletePic(string path, string id)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = "delete from pic_tb where path = @path and pic_ID = @id";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    {
                        cmd.Parameters.AddWithValue("@path", path);
                        cmd.Parameters.AddWithValue("@id", id);
                        return cmd.ExecuteNonQuery() > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public List<string> getPicList(string id)
        {
            List<string> list = new List<string>();
            try
            {
                if (DBConnection())
                {
                    string sql = "select path from pic_tb where pic_ID = @id";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    {
                        cmd.Parameters.AddWithValue("@id", id);
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                list.Add(reader.GetString(0));
                            }
                        }
                    }
                }
            }
            catch { }
            finally { this.Dispose(); }
            return list;
        }


        public bool SaveResults(ResultModel result)
        {
            bool res = false;
            int maxValue = 0;

            if (DBConnection())
            {
                using (var sqlTransaction = Conn.BeginTransaction())
                {
                    try
                    {
                        string sql = "SELECT MAX(CAST(pic_Num AS INTEGER)) FROM pic_tb";
                        using (var cmd = new SQLiteCommand(sql, Conn, sqlTransaction))
                        {
                            var obj = cmd.ExecuteScalar();
                            maxValue = (obj != DBNull.Value && obj != null) ? Convert.ToInt32(obj) + 1 : 1;
                        }

                        string querySql = "SELECT COUNT(*) FROM record_tb WHERE rec_ID = @id";
                        int count = 0;
                        using (var cmd = new SQLiteCommand(querySql, Conn, sqlTransaction))
                        {
                            cmd.Parameters.AddWithValue("@id", result.Id);
                            count = Convert.ToInt32(cmd.ExecuteScalar());
                        }

                        if (count <= 0)
                        {
                            string sql1 = "INSERT INTO record_tb(rec_ID, results, cdate) VALUES (@id, @desc, @date)";
                            using (var cmd = new SQLiteCommand(sql1, Conn, sqlTransaction))
                            {
                                cmd.Parameters.AddWithValue("@id", result.Id);
                                cmd.Parameters.AddWithValue("@desc", result.Description);
                                cmd.Parameters.AddWithValue("@date", result.Date);
                                cmd.ExecuteNonQuery();
                            }
                        }

                        string sql2 = "INSERT INTO pic_tb(pic_ID, pic_Num, name, path, pdate) VALUES (@id, @num, '', @path, @date)";
                        using (var cmd = new SQLiteCommand(sql2, Conn, sqlTransaction))
                        {
                            cmd.Parameters.AddWithValue("@id", result.Id);
                            cmd.Parameters.AddWithValue("@num", maxValue.ToString());
                            cmd.Parameters.AddWithValue("@path", result.Path);
                            cmd.Parameters.AddWithValue("@date", result.Date);
                            cmd.ExecuteNonQuery();
                        }

                        sqlTransaction.Commit();
                        res = true;
                    }
                    catch
                    {
                        sqlTransaction.Rollback();
                        res = false;
                    }
                    finally
                    {
                        Dispose();
                    }
                }
            }

            return res;
        }

        public bool SaveUser(UserModel user)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = "INSERT INTO user_tb(user_ID, user_name, pwd, is_admin) VALUES (@id, @name, @pwd, @admin)";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    {
                        cmd.Parameters.AddWithValue("@id", user.UserName);
                        cmd.Parameters.AddWithValue("@name", user.UserName);
                        cmd.Parameters.AddWithValue("@pwd", Md5Provider.GetMD5String(user.Password + "@" + user.UserName.ToLower()));
                        cmd.Parameters.AddWithValue("@admin", user.IsAdmin.ToString());
                        return cmd.ExecuteNonQuery() > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public bool EditPatientInfo(PatientModel patient, string currentid)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = "UPDATE patient_tb SET p_ID = @p_ID, p_name = @p_name, sex = @sex, birth = @birth, adress = @adress, telphone = @telphone, email = @email, updateDate = @updateDate WHERE p_ID = @currentid";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    {
                        cmd.Parameters.AddWithValue("@p_ID", patient.P_ID);
                        cmd.Parameters.AddWithValue("@p_name", patient.Name);
                        cmd.Parameters.AddWithValue("@sex", patient.SEX);
                        cmd.Parameters.AddWithValue("@birth", patient.Birth);
                        cmd.Parameters.AddWithValue("@adress", patient.Adreess);
                        cmd.Parameters.AddWithValue("@telphone", patient.PhoneNum);
                        cmd.Parameters.AddWithValue("@email", patient.Email);
                        cmd.Parameters.AddWithValue("@updateDate", DateTime.Now);
                        cmd.Parameters.AddWithValue("@currentid", currentid);
                        return cmd.ExecuteNonQuery() > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }


        public bool DeletePatient(PatientModel patient)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = $"delete from  patient_tb where p_ID ='{patient.P_ID}'";
                    string sql2 = $"delete from  pic_tb where pic_ID ='{patient.P_ID}'";
                    
                    SQLiteCommand cmd = new SQLiteCommand(sql, Conn);
                    SQLiteCommand cmd2 = new SQLiteCommand(sql2, Conn);
                    int res = cmd.ExecuteNonQuery();
                    int res2 = cmd2.ExecuteNonQuery();
                    if (res > 0)
                    {
                        return true;
                    }

                }
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public List<PatientModel> GetPatients(string creator)
        {
            List<PatientModel> patientList = new List<PatientModel>();
            try
            {
                if (DBConnection())
                {
                    string sql = "SELECT * FROM patient_tb WHERE creator = @creator";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    {
                        cmd.Parameters.AddWithValue("@creator", creator);
                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                patientList.Add(new PatientModel()
                                {
                                    Name = reader["p_name"].ToString(),
                                    P_ID = reader["p_ID"].ToString(),
                                    SEX = reader["sex"].ToString(),
                                    Birth = Convert.ToDateTime(reader["birth"]),
                                    Adreess = reader["adress"].ToString(),
                                    PhoneNum = reader["telphone"].ToString(),
                                    Email = reader["email"].ToString(),
                                    Creator = reader["creator"].ToString(),
                                    InsertDate = Convert.ToDateTime(reader["insertDate"])
                                });
                            }
                        }
                    }
                }
                return patientList;
            }
            catch
            {
                throw;
            }
            finally
            {
                this.Dispose();
            }
        }

        public bool PatidExist(string patid)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = "SELECT COUNT(*) FROM patient_tb WHERE p_ID = @patid";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    {
                        cmd.Parameters.AddWithValue("@patid", patid);
                        int count = Convert.ToInt32(cmd.ExecuteScalar());
                        return count > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public bool PicExist(string id)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = "SELECT COUNT(*) FROM pic_tb WHERE pic_ID = @id";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    {
                        cmd.Parameters.AddWithValue("@id", id);
                        int count = Convert.ToInt32(cmd.ExecuteScalar());
                        return count > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public bool UserExist(string username)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = "SELECT COUNT(*) FROM user_tb WHERE user_ID = @id";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    {
                        cmd.Parameters.AddWithValue("@id", username);
                        int count = Convert.ToInt32(cmd.ExecuteScalar());
                        return count > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }


        public bool UpdateUserPassword(string userName, string newPassword, int isAdmin)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = "UPDATE user_tb SET pwd = @pwd, is_admin = @admin WHERE user_name = @name";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    {
                        cmd.Parameters.AddWithValue("@pwd", Md5Provider.GetMD5String(newPassword + "@" + userName.ToLower()));
                        cmd.Parameters.AddWithValue("@admin", isAdmin);
                        cmd.Parameters.AddWithValue("@name", userName);
                        return cmd.ExecuteNonQuery() > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public bool DeleteUser(UserModel user)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = "DELETE FROM user_tb WHERE user_name = @name";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    {
                        cmd.Parameters.AddWithValue("@name", user.UserName);
                        return cmd.ExecuteNonQuery() > 0;
                    }
                }
            }
            catch
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public List<UserModel> GetAllUsers()
        {
            List<UserModel> users = new List<UserModel>();
            try
            {
                if (DBConnection())
                {
                    string sql = "SELECT user_name, is_admin FROM user_tb";
                    using (var cmd = new SQLiteCommand(sql, Conn))
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            users.Add(new UserModel
                            {
                                UserName = reader["user_name"].ToString(),
                                IsAdmin = Convert.ToInt32(reader["is_admin"])
                            });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"获取用户列表失败: {ex.Message}");
            }
            finally
            {
                this.Dispose();
            }
            return users;
        }



    }
}
